package com.compty.paint.manager;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.compty.core.hibernate.HibernateEntityDao;
import com.compty.paint.domain.Gallery;
import com.compty.paint.domain.Painter;

/**
 * @Todo:画廊Manager
 * @author: zhuqh
 * @CreateTime:2011-8-15 上午09:13:27
 */
public class GalleryManager extends HibernateEntityDao<Gallery> {
	
	/**
	 *@Todo:查找所有省份ID集合
	 *@author:zhuqh
	 *@CreateTime:2011-8-20 下午04:49:44
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List listProvinceId() {
		
		return (List) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				
				String sql = "select province_id,count(id) as total from t_gallery group by province_id order by total desc";
				
				SQLQuery query = session.createSQLQuery(sql);
				
				List provinceIdList =  query.addScalar("province_id", Hibernate.LONG).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
				return provinceIdList;
			}
	    });
	}

	/**
	 *@Todo:由省份ID查找画廊列表
	 *@author:zhuqh
	 *@CreateTime:2011-8-20 下午04:49:05
	 * @param pId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List findGalleryByProvinceId(Long pId) {
		// TODO Auto-generated method stub
		String hql = "from Gallery g where g.provinceInfo.id = ? order by g.id desc";
		
		return super.find(hql, pId);
	}
	
	/**
	 *@Todo:查找最新(推荐)画廊
	 *@author:zhuqh
	 *@CreateTime:2011-8-20 下午04:47:41
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Gallery findNewestGallery(){
		
		String hql = "from Gallery g  order by g.id desc";
		Query query = super.createQuery(hql);
		query.setMaxResults(1);
		List list = query.list();
		if(list != null && list.size() >0 ){
			Object obj = list.get(0);
			return (Gallery)obj;
		}else{
			return null;
		}
	}

	/**
	 *@Todo:查找画廊所有作品
	 *@author:zhuqh
	 *@CreateTime:2011-8-20 下午04:48:09
	 * @param galleryId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List findPaintingsByGalleryId(Long galleryId) {
		String hql = "select p from Gallery g inner join g.paintings p  where g.id=?";
		
		List list = find(hql, galleryId);
		
		return list;
	}
	
	/**
	 *@Todo:由用户ID查找画廊信息
	 *@author:zhuqh
	 *@CreateTime:2011-8-20 下午04:51:57
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Gallery findGalleryByUserId(Long userId){
		
		String hql = "from Gallery g where g.entryPerson.id = ?";
		
		List list = find(hql, userId);
		
		if(list != null && list.size()>0){
			return (Gallery) list.get(0);
		}
		
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<Painter> findPaintersByGalleryId(Long galleryId){
		
		String hql = "select g.entryPerson.id as id  from Gallery g where g.id=?";
		
		List list = find(hql, galleryId);
		if(list != null && list.size()>0){
			Long userId =   (Long) list.get(0);
			hql = "from Painter where entryPerson.id = ?";
			List painterList = find(hql, userId);
			
			return painterList;
		}
		return null;
	}
	
	/**
	 * galleryId 画廊、美术馆ID
	 */
	public void removeById(Serializable galleryId){
		
		String hql = "update Gallery set entryPerson = null where id = ?";
		
		Query q = super.createQuery(hql);
		q.setParameter(0, galleryId);
		q.executeUpdate();
		
		String hql3 = "delete Gallery  where id = ?";
		
		Query q3 = super.createQuery(hql3);
		q3.setParameter(0, galleryId );
		q3.executeUpdate();
		
	}
	
	public void removeByUserId(Serializable userId){
		
	}
	
}
